Show AllThe workbook you have opened (called a destination file) contains links to other workbooks or files (called source files). If the source has changed, then the workbook you are opening uses data that is out of date.
Workbook contains links
Destination file. This workbook that was just opened.
Links. Formulas that reference a location in the source.
Source file, such as another workbook. If someone else has changed a value in a cell, Excel does not know to look for the new value unless you tell it to.
If you don't remember creating links
Click Don't Update. It is possible to accidentally create a link by moving or copying a range, worksheet, or chart between workbooks. Open the workbook, and then look for the links that were created, and delete them.
Important When you break a link to a source, all formulas that that use the source are converted to their current value. For example, the link =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want to save a version of the file before you start.
On the Edit menu, click Links.
In the Source list, click the link you want to break.
To select multiple linked objects, hold down CTRL and click each linked object.
To select all links, press CTRL+A.
Click Break Link.
If the link used a defined name, the name is not automatically removed. You may want to delete the name as well.
On the Insert menu, point to Name, and then click Define.
In the Names in workbook list, click the name you want to change.
Do one of the following:
Type the new name for the reference, and then click Add.
Click the original name, and then click Delete.
Change it in the Refers to box.
Click Delete.
If you use an external data range, a parameter of a query may also use data from another workbook. You may want to check for and remove any of these type of links.
Control the startup prompt for updating links
Don't ask whether to update links when I open any workbook, and update links automatically
This option is for the current user only, and affects every workbook opened. Other users of the workbook are not affected. This option also affects links to other programs.
On the Tools menu, click Options, and then click the Edit tab.
Clear the Ask to update automatic links check box. If the box is cleared, the links are automatically updated, and no prompt is displayed.
Don't ask to update links for this workbook, and let me control whether links are updated
Warning This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know the data is out of date.
On Edit menu, click Links.
Click Startup Prompt.
Select the option you want.
Note You will still be notified if there are any broken links.
Replace a single formula with its calculated value
Caution When you replace a formula with its value, Microsoft Excel permanently removes the formula. If you accidentally replace a formula with a value and want to restore the formula, click Undo
immediately after you enter or paste the value.
Select the cell that contains the formula.
If the formula is an array formula, select the range that contains the array formula.
Click any cell in the array range.
On the Edit menu, click Go To.
Click Special.
Click Current array.
Click Copy
.
Click Paste
on the Standard
toolbar.
Click the arrow next to Paste Options
, and then click Values.
If you know you are not connected to the source
Click Don't Update. Microsoft Excel cannot connect to a source if it is not there. For example, the source may be on a network, and you may not be connected to the network.
If you want the data as it already exists, and don't want it replaced with new data
Click Don't Update.
If you tried to update last time, and it took too long
Click Don't Update. If the data does not need to be the most current, you can save time by not updating all the links. After opening the workbook, on the Edit menu, click Links, and then update links only from the sources you need.
If someone else created the workbook, and you don't know why you are seeing this prompt
Click Update. Contact the author of the workbook. You can also investigate what links are in the workbook. On the Edit menu, click Links.
If you answer the prompt the same way every time, and don't want to see it again
You can answer the startup prompt in a consistent way, and avoid seeing it for this workbook.
Don't prompt for all workbooks I open, and update the links automatically
This option is for the current user only, and affects every workbook opened. Other users of the workbook are not affected.
On the Tools menu, click Options, and then click the Edit tab.
Clear the Ask to update automatic links check box. If the box is cleared, the links are automatically updated, and no alert is displayed.
Prompt in the same way for every user of the workbook
Warning This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know the data is out of date.
On Edit menu, click Links.
Click Startup Prompt.
Select the option you want.
Note You will still be notified if there are any broken links.
If you know the workbook Excel is trying to connect to is available
Click Update. This ensures you have the latest data.
If you are using a parameter query
A link to a parameter query cannot be updated unless the source workbook is open.
Click Don't Update.
Close the destination workbook.
Open the source workbook.
Open the destination workbook.
Click Update.